mysql collection of commonly used commands [absolute essence]

1. Connect MYSQL.

Format: mysql-h host address -u username -p user password

1. Connect to MYSQL on the local machine.

First open the DOS window, then enter the mysql\bin directory, then enter the command mysql-u root-p, and enter the command mysql-u root-p.

If you have just installed MYSQL, the superuser root does not have a password, so press enter to enter MYSQL. The prompt for MYSQL is mysql >

2. Connect to MYSQL on the remote host. Assume that the IP of the remote host is: 110.110.110.110, the user name is root, and the password is abcd123. Then type the following command:

110.110.110-u root-p 123; (note: there is no space between u and root, and the others are the same)

3. Exit MYSQL command: exit (enter)

2. Change your password.

Format: mysqladmin-u username -p old password password new password

1. Give root the password ab12. First enter the directory mysql\bin under DOS and then type the following command

mysqladmin -u root -password ab12

Note: since root does not have a password at the beginning, the -p old password 1 entry can be omitted.

2. Change the password of root to djg345.

mysqladmin -u root -p ab12 password djg345

 

3. Add new users.
(note: unlike above, the following commands are terminated with a semicolon because they are in the MYSQL environment.)

Format: grant select on database.* to username @ login host identified by "password"

1. Add one user test1 password to abc, so that he can log in on any host and have the right to query, insert, modify and delete all databases. First connect to MYSQL with root, then type the following command:

grant select insert, update, delete on *. * to test1 @ "%" Identified by "abc";

If someone knows the password for test1, they can log into your mysql database from any computer on internet and do whatever they want with your data.

2. Add one user whose test2 password is abc, so that he can only log in on localhost, and can query, insert, modify and delete the database mydb (localhost refers to the local host, that is, the host where MYSQL database is located).

This way, even if the user knows test2 password, he cannot access the database directly from internet, but only through web page on the MYSQL host.

grant select insert, update, delete on mydb. * to test2 @ localhost identified by "abc";

If you don't want test2 to have a password, you can type another command to cancel the password.

grant select insert, update, delete on mydb. * to test2 @ localhost identified by "";

In the next installment, I'm going to talk about database operations in MYSQL. Note: you must first log into MYSQL. The following operations are done at the prompt of MYSQL, and each command ends with a semicolon.

1. Operation skills

1, if you type the command, return found that you forgot to add a semicolon, you do not have to retype the command 1 times, as long as a semicolon return can be.

This means that you can type a complete command into a few lines and then end it with a semicolon (OK).

2, you can use the cursor up and down key to bring up the previous command.

2. Display commands

1. Display the database list in the current database server:

mysql > SHOW DATABASES;

Note: the mysql library contains the system information of MYSQL. We changed the password and added new users, and we are actually operating with this library.

2. Display the data table in the database:

mysql > USE library name;
mysql > SHOW TABLES;

3. Structure of display data table:

mysql > DESCRIBE table name;

4. Database establishment:

mysql > CREATE DATABASE library name;

5. Data table:

mysql > USE library name;
mysql > CREATE TABLE table name (field name VARCHAR(20), field name CHAR(1));

6. Delete the database:

mysql > DROP DATABASE library name;

7. Delete data table:

mysql > DROP TABLE table name;

8. Empty the records in the table:

mysql > DELETE FROM table name;

9. Show the records in the table:

mysql > SELECT * FROM table names;

10. Insert records into the table:

mysql > INSERT INTO table name VALUES (" hyq ", "M");

11. Update the data in the table:

mysql- > UPDATE table name SET field name 1='a', field name 2='b' WHERE field name 3='c';

12. Load the data into the data table by text:

mysql > LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE table name;

13. Import.sql file command:

mysql > USE database name;
mysql > SOURCE d:/mysql.sql;

14. Modify root password at the command line:

mysql > UPDATE SET password=PASSWORD(' new password ') WHERE User='root';
mysql > FLUSH PRIVILEGES;

15. Database name of use:

mysql > SELECT DATABASE();

16. Display the current user:

mysql > SELECT USER();

3. One instance of database building and table building and data insertion

drop database if exists school; // delete if SCHOOL exists

create database school; // set up SCHOOL library

use school; // open SCHOOL

create table teacher // create table TEACHER
(
id int(3) auto_increment not null primary key,
name char(10) not null,
address varchar (50) default 'shenzhen',
year date
); // the table is finished

// below are the insert fields
insert into teacher values (", 'allen', 'dalian 1', '1976-10-10');
insert into teacher values (", 'jack', 'dalian 2 middle', '1975-12-23');

This is fine if you type the above command at the mysql prompt, but it's not easy to debug.

(1) you can write the above command to a text file, assuming school.sql, then copy to c:\\, enter directory \\mysql\\bin in DOS state, and type the following command:

mysql-uroot-p password < c:\\school.sql

If successful, blank 1 line without any display; If there is an error, there will be a prompt. The above command has been debugged, you just need to remove the comments of // to use it.

(2) or use mysql after entering the command line > source c: \ \ school sql; You can also import the school.sql file into the database.

4. Transfer text data to database

1, text data should conform to the format: field data is separated by tab key, null value is replaced by \\n.

3 rose dalian 2 middle school 1976-10-10

4 mike dalian 1 1975-12-23

Suppose you save these two sets of data as school.txt, in the c root directory.

2, data incoming command load data local infile "c:\\ school.txt" into table table name;

Note: you'd better copy the file to the directory \\mysql\\bin and type the library with the use command first.

5. Backup database :(command to be executed in the DOS \\mysql\\bin directory)

Export the entire database

The export file is stored in the mysql\bin directory by default

mysqldump-u user name -p database name > Exported file name

mysqldump -u user_name -p123456 database_name > outfile_name.sql

2. Export 1 table

mysqldump-u user name -p database name table name > Exported file name

mysqldump -u user_name -p database_name table_name > outfile_name.sql

Export a database structure

mysqldump-u user_name-p-d > outfile_name.sql

-d has no data to add 1 drop table to each create statement

4. Export with language parameters

mysqldump-uroot-set =latin1, set-charset =gbk, skip-opt database_name > outfile_name.sql

1. Back up the database
mysqldump -uroot -p test_db > test_db.sql
2. Restore the database
mysql -uroot -p test_db < test_db.sql
3. Create permissions
grant all privileges on test_db.* to test_db@'localhost' identified by '123456';
Compatible with mysql 4.1 previous mode:
update mysql.user set password=old_password('123456') where user='test_db';
Forget your password
Add "skip-grant-tables" in the "mysqld" configuration section of the "my.cnf" or "my.ini" file, and then restart mysql to log in and change the root password.